package main;

import java.sql.*;
import java.util.Scanner;

/**
 * Created by freewheel on 2016/10/10.
 */
public class SalespersonHelper {

    private static final int SEARCH_PART = 1;
    private static final int SELL_PART = 2;
    private static final int RETURN_MAIN = 3;

    public static void printSalespersonOperationsMenu() {
        String menu = "-----Operations for salesperson menu-----\n" +
                "What kinds of operation would you like to perform?\n" +
                "1. Search for parts\n" +
                "2. Sell a part\n" +
                "3. Return to main menu\n" +
                "Enter your Choice: ";
        System.out.println(menu);
    }

    public static void performOperationsByOption(int option) {
        switch (option) {
            case SEARCH_PART: {
                searchPart();
                break;
            }
            case SELL_PART: {
                sellPart();
                break;
            }
            case RETURN_MAIN: {
                Entry.enterMainMenu();
                return;
            }
            default:
                System.out.println("\nPlease Input Number 1  to 3 !");
                break;
        }
        Entry.showMenuByRole(Role.SALESPERSON);

    }

    private static void sellPart() {
        System.out.println("Enter The Part ID: ");
        Scanner sc = Entry.getScanner();
        if (sc.hasNextInt()) {
            int partId = sc.nextInt();
            System.out.println("Enter the Salesperson ID: ");

            if (sc.hasNextInt()) {
                int salespersonId = sc.nextInt();

                sellPart(partId, salespersonId);
            }
        }
    }

    private static void sellPart(int partId, int salespersonId) {
        Connection conn = DBHelper.getConnection();
        PreparedStatement queryStatement = null;
        PreparedStatement updatePartStm = null;
        PreparedStatement insertTransctionStm = null;
        try {
            if (!DBHelper.tableExist("part")) {
                System.out.println("WARNING: required table not exist !");
                return;
            }
            queryStatement = conn.prepareStatement("Select * from part Where pID=?");
            queryStatement.setInt(1, partId);
            ResultSet resultSet = queryStatement.executeQuery();
            if (resultSet.next()) {
                String partName = resultSet.getString("pName");
                int quantity = resultSet.getInt("pAvailableQuantity");
                if (quantity > 0) {
                    try {
                        conn.setAutoCommit(false);

                        updatePartStm = conn.prepareStatement("Update part P Set P.pAvailableQuantity = P.pAvailableQuantity -1 Where P.pID = ? AND P.pAvailableQuantity > 0;");
                        updatePartStm.setInt(1, partId);
                        int updateResult = updatePartStm.executeUpdate();
                        if (updateResult == 0) {
                            System.out.println("Sorry ! The Part " + partName + " has not enough repertory.");
                        } else {
                            insertTransctionStm = conn.prepareStatement("Insert into transaction (pID,sID,tDate)Values(?,?,?);");
                            insertTransctionStm.setInt(1, partId);
                            insertTransctionStm.setInt(2, salespersonId);
                            insertTransctionStm.setDate(3, new Date(new java.util.Date().getTime()));
                            insertTransctionStm.execute();

                            queryStatement.setInt(1, partId);
                            ResultSet latestRs = queryStatement.executeQuery();
                            if (latestRs.next()) {
                                System.out.println("Product: " + latestRs.getString("pName") + " Remaining Quantity: " + latestRs.getInt("pAvailableQuantity"));
                            }
                            latestRs.close();
                        }

                        conn.commit();
                    }catch (SQLException e){
                        System.err.println("Opearation Fail: "+e.getMessage());
                        conn.rollback();
                    }

                } else {
                    System.out.println("Sorry ! The Part " + partName + " has not enough repertory.");
                }
            } else {
                System.out.println("Sorry ! Not such Part exist");
            }
            resultSet.close();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (queryStatement != null) {
                try {
                    queryStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (updatePartStm != null) {
                try {
                    updatePartStm.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (insertTransctionStm != null) {
                try {
                    insertTransctionStm.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            try {
                conn.setAutoCommit(true);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private static void searchPart() {
        System.out.println("Choose the search criterion: \n" +
                "1. Part Name \n" +
                "2. Manufacturer Name \n" +
                "3. Cancel \n" +
                "Enter your choice: ");
        Scanner sc = Entry.getScanner();
        if (sc.hasNextInt()) {
            int choice = sc.nextInt();
            System.out.println("Type in the Keyword: ");
            sc.nextLine();
            if (choice == 1) {
                if (sc.hasNextLine()) {
                    String partKeyWord = sc.nextLine();
                    System.out.println("Choose ordering: \n" +
                            "1. By price, ascending order \n" +
                            "2. By price, descending order \n" +
                            "Enter your choice: ");
                    if (sc.hasNextInt()) {
                        int orderChoice = sc.nextInt();
                        if (orderChoice == 1) {
                            searchPartByPartKey(partKeyWord, "ASC");
                        } else {
                            searchPartByPartKey(partKeyWord, "DESC");
                        }
                    }
                }
            } else if (choice == 2) {
                if (sc.hasNextLine()) {
                    String manufKeyWord = sc.nextLine();
                    System.out.println("Choose ordering: \n" +
                            "1. By price, ascending order \n" +
                            "2. By price, descending order \n" +
                            "Enter your choice: ");
                    if (sc.hasNextInt()) {
                        int orderChoice = sc.nextInt();
                        if (orderChoice == 1) {
                            searchPartByManufKey(manufKeyWord, "ASC");
                        } else {
                            searchPartByManufKey(manufKeyWord, "DESC");
                        }
                    }
                }
            } else if (choice == 3) {

            } else {
                System.out.println("Please Input Number 1");
                searchPart();
            }
        }
    }

    private static void searchPartByManufKey(String manufKeyWord, String order) {
        String sql = "Select P.pID, P.pName, M.mName, C.cName, P.pAvailableQuantity, P.pWarrantyPeriod, P.pPrice " +
                "From part P, manufacturer M, category C " +
                "Where P.cID = C.cID AND P.mID = M.mID AND M.mName LIKE '%" + manufKeyWord + "%' " +
                "ORDER BY P.pPrice " + order;
        searchPartBySql(sql);
    }

    private static void searchPartByPartKey(String partKeyWord, String order) {
        String sql = "Select P.pID, P.pName, M.mName, C.cName, P.pAvailableQuantity, P.pWarrantyPeriod, P.pPrice " +
                "From part P, manufacturer M, category C " +
                "Where P.cID = C.cID AND P.mID = M.mID AND P.pName LIKE '%" + partKeyWord + "%' " +
                "ORDER BY P.pPrice " + order;
        searchPartBySql(sql);
    }

    private static void searchPartBySql(String sql) {
        Connection conn = DBHelper.getConnection();
        System.out.println("| ID | Name | Manufacturer | Category | Quantity | Warranty | Price | ");

        Statement statement = null;
        try {
            if (!DBHelper.tableExist("part")) {
                System.out.println("WARNING: required table not exist !");
                return;
            }
            statement = conn.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                System.out.println("| " + resultSet.getInt(1) +
                        " | " + resultSet.getString(2) +
                        " | " + resultSet.getString(3) +
                        " | " + resultSet.getString(4) +
                        " | " + resultSet.getInt(5) +
                        " | " + resultSet.getInt(6) +
                        " | " + resultSet.getInt(7) +
                        " |");
            }
            resultSet.close();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}
